package com.lalala;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

/**
 * Description:
 *
 * @Author: ZhaoNing
 * @Date 2017/6/30 11:12
 */
public class MysqlConnecter {



    public final static String DRIVER   = "com.mysql.jdbc.Driver";
    public final static String URLPRE      = "jdbc:mysql://";
    public final static int    TABLELEN = 10;
    private Connection connection = null;
    private boolean connected = false;

    public MysqlConnecter(String address,String DBNAME,String USERNAME,String PASSWORD) {
        String URL = URLPRE + address +"/"+ DBNAME;
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println("ERROR AT MysqlConnecter");
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            connected = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void close() throws SQLException {
        connection.close();
    }
    public int insert(String sql)
    {
        int lineNum = 0;
        if (!connected) return 0;
        try{
            PreparedStatement preStmt = connection.prepareStatement(sql);
            lineNum = preStmt.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return lineNum;
    }

    public int update(String sql)
    {
        int lineNum = 0;
        if (!connected) return 0;
        try{
            PreparedStatement preStmt = connection.prepareStatement(sql);
            lineNum = preStmt.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return lineNum;
    }
    public ArrayList<Map<String, String>> select(String sql, String tableName)
    {
        ArrayList<Map<String, String>> result = new ArrayList<Map<String, String>>();

        try
        {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            String[] frame = getFrame(tableName);
            while (rs.next())
            {
                Map<String, String> tmp = new HashMap<String, String>();
                for (String key : frame) {
                    if (key == "#") break;
                    tmp.put(key, rs.getString(key));
                }
                result.add(tmp);
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return result;
    }
    public int delete(String sql)
    {
        int lineNum = 0;
        try
        {
            Statement stmt = connection.createStatement();
            lineNum = stmt.executeUpdate(sql);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return lineNum;
    }
    // 获取当前表的关键字，并以字符串数组的形式返回：如“username”，“id“等
    private String[] getFrame(String tableName) {
        String[] result = new String[TABLELEN];
        try
        {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("show columns from " + tableName);
            int i = 0;
            while (rs.next())
            {
                result[i++] = rs.getString(1);
            }
            result[i] = "#";
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return result;
    }

}
